const Koa = require('koa')
const cors = require('koa2-cors')
const Router = require('koa-router')
const bodyParser = require('koa-bodyparser')
const sql = require('mssql')
const app = new Koa()
const router = new Router()
const request = require('request')




// 模拟发送请求
// 创建连接
sql.connect('mssql://YJSMD_Login:YJS@123@192.168.0.199/ksoa').then(function () {

    // 对接第三方接口
    const httprequest = async (url, uploadVal) => {
        const crypto = require('crypto')
        const md5 = crypto.createHash('md5')
        let appKey = 'edb94270a6c9f3206c316f55682e92a1'
        let version = '2.0'
        let signSecret = '52e32a2f17355945a8c71810fb172eec'
        let currentDate = Date.parse(new Date())
        let bizParams = JSON.stringify(uploadVal)
        let needMd5 = `appKey${appKey}bizParams${bizParams}currentDate${currentDate}signSecret${signSecret}version${version}`;
        let sign = md5.update(needMd5, 'utf8').digest('hex')
        let getCode = () => new Promise((resolve, reject) => request.post({
            url: url,
            headers: {
                "content-type": "application/x-www-form-urlencoded",
            },
            form: {
                appKey: appKey,
                version: version,
                signSecret: signSecret,
                currentDate: currentDate,
                bizParams: bizParams,
                sign: sign
            }
        }, function (error, response, body) {
            resolve(JSON.parse(body).code)
        }));  
        return await getCode()
    }
    // 将自己数据库获得商品信息的数据转成第三方需要的字段的下标,添加的下标是小写
    const transformShopInforIndex = async (obj, action) => {
        let shopInfor = []
        let shop_num = []
        for (let value of obj) {
            shopInfor.push({
                erpsn: value.spid,//药品内码
                name: value.spmch,//药品名称
                specifications: value.shpgg,//规格
                unit: value.dw,//单位
                manufacture: value.shpchd,//厂家
                base_price: value.hshj,//含税价
                // g_status: value.beactive,//商品状态
                g_status: value.beactive == '是' ? 1 : 0,
                oper_type: action //操作类型，1新增，2修改，3删除
            })
            shop_num.push({
                erp_sn: value.spid,//药品内码
                erp_id_no: value.spid,//erp的库存编码id
                number: value.kcshl,//药品库存
                oper_type: action //操作类型，1新增，2修改，3删除
            })
        }
        return { shopInfor: shopInfor, shop_num: shop_num }
    }
      // 将自己数据库获得商品信息的数据转成第三方需要的字段的下标,添加的下标是大写
      const transformShopInforLargerIndex = async (obj, action) => {
        let shopInfor = []
        let shop_num = []
        for (let value of obj) {
            shopInfor.push({
                erpsn: value.SPID,//药品内码
                name: value.SPMCH,//药品名称
                specifications: value.SHPGG,//规格
                unit: value.DW,//单位
                manufacture: value.SHPCHD,//厂家
                base_price: value.HSHJ,//含税价
                // g_status: value.beactive,//商品状态
                g_status: value.BEACTIVE == '是' ? 1 : 0,
                oper_type: action //操作类型，1新增，2修改，3删除
            })
            shop_num.push({
                erp_sn: value.SPID,//药品内码
                erp_id_no: value.SPID,//erp的库存编码id
                number: value.KCSHL,//药品库存
                oper_type: action //操作类型，1新增，2修改，3删除
            })
        }
        return { shopInfor: shopInfor, shop_num: shop_num }
    }
    // 同步商品信息
    const synchroShopInfor = async () => {
        let sqlShopInfor = `SELECT t1.spid,t1.spbh,t1.beactive,t1.spmch, t1.dw, t1.shpchd, t1.shpgg, t1.pizhwh, t1.jlgg, t1.kcshl, t1.chbdj, t1.zhjj, AVG(t1.hshj) as hshj from (SELECT  K.spid, K.spbh, K.beactive, K.spmch, K.dw, K.shpchd, K.shpgg, K.pizhwh, K.jlgg, C.kcshl, C.chbdj, C.zhjj,X.dj,X.hshj FROM spkfk K LEFT JOIN spkfjc C ON K.spid=C.spid LEFT JOIN pf_ckmx X ON K.spid = X.spid WHERE K.spid <>ALL (SELECT SPID FROM erpshopinfor))as t1 group by t1.spid ,t1.spbh,t1.beactive,t1.spmch,t1.dw, t1.shpchd, t1.shpgg, t1.pizhwh, t1.jlgg, t1.kcshl, t1.chbdj, t1.zhjj`
        let results = await sql.query(sqlShopInfor)
        let shopInforVal = await transformShopInforIndex(results.recordset, 1)
        let shopInfor = shopInforVal.shopInfor
        let stockInfor = shopInforVal.shop_num
        let pushShopInfor = []
        let pushStockInfor = []
        for (let i = 0; i < shopInfor.length; i = i + 1000) {
            pushShopInfor.push(shopInfor.slice(i, i + 1000))
            pushStockInfor.push(stockInfor.slice(i, i + 1000))
        }
        pushStockInfor.forEach((e) => {        
            httprequest(`http://erpsrv.mypharma.com/inventory/batchSnyc`, e)
        })
        pushShopInfor.forEach((e) => {        
            httprequest(`http://erpsrv.mypharma.com/goods/batchSnyc`, e)
        })
    }
   
 
    // 将自己数据库获得客户信息的数据转成第三方需要的字段的下标
    const transformCustomerIndex = async (obj, action) => {
        let customerInfor = []
        for (let value of obj) {
            customerInfor.push({
                internal_code: value.dwbh,//客户内码
                code: value.danwbh,//企业编码
                name: value.dwmch,//企业名称
                license_no: value.yingyzz,//工商注册号
                license_endtime: value.gsp_yyzzyxq,//工商注册号有效期
                allow_no: value.xvkz,//经营许可号
                allow_endtime: value.gsp_xkzyxq,//经营许可有效期
                gsp_no: value.gspzs,//gsp证书号
                gsp_endtime: value.gsp_gspyxq,//gsp证书有效期
                legal: value.frdb,//企业法人
                address: value.dzhdh,//企业地址
                oper_type: action //操作类型，1新增，2修改，3删除
            })
        }
        return customerInfor
    }
    // 同步客户信息
    const synchroCustomerInfor = async () => {
        let sqlCustomerInfor = `SELECT k.dwbh,k.danwbh,k.dwmch,k.yingyzz,k.gsp_yyzzyxq,k.xvkz,k.gsp_xkzyxq,k.gspzs,k.gsp_gspyxq,k.frdb,k.dzhdh FROM mchk K`
        let results = await sql.query(sqlCustomerInfor)
        let customerInforVal = await transformCustomerIndex(results.recordset, 1)
        let pushCustomerInfor = []
        for (let i = 0; i < customerInforVal.length; i = i + 1000) {
            pushCustomerInfor.push(customerInforVal.slice(i, i + 1000))
        }
        pushCustomerInfor.forEach((e) => {        
            httprequest(`http://erpsrv.mypharma.com/customer/batchSnyc`, e)
        })
    }
    synchroShopInfor()
    synchroCustomerInfor()
    //每半个小时同步一下数据 
    setInterval(() => {
        synchroShopInfor()
        synchroCustomerInfor()
    },1800000)

    // 从原始商家数据库获取商品信息
    router.get('/getShopInfor', async ctx => {
        try {
            const body = ctx.query
            // 获取所有信息的       
            let startAllSql = `SELECT * FROM (SELECT t1.spid,t1.spbh,t1.beactive,t1.spmch, t1.dw, t1.shpchd, t1.shpgg, t1.pizhwh, t1.jlgg, t1.kcshl, t1.chbdj, t1.zhjj, AVG(t1.hshj) as hshj from (SELECT  K.spid, K.spbh, K.beactive, K.spmch, K.dw, K.shpchd, K.shpgg, K.pizhwh, K.jlgg, C.kcshl, C.chbdj, C.zhjj,X.dj,X.hshj FROM spkfk K LEFT JOIN spkfjc C ON K.spid=C.spid LEFT JOIN pf_ckmx X ON K.spid = X.spid WHERE K.spid <>ALL (SELECT SPID FROM erpshopinfor)`
            let endAllSql = `) as t1 group by t1.spid ,t1.spbh,t1.beactive,t1.spmch,t1.dw, t1.shpchd, t1.shpgg, t1.pizhwh, t1.jlgg, t1.kcshl, t1.chbdj, t1.zhjj) AS t2`
            // 创建分页和搜索的sql语句
            let startSql = `SELECT * FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY t2.spid DESC) AS PX FROM (SELECT t1.spid,t1.spbh,t1.beactive,t1.spmch, t1.dw, t1.shpchd, t1.shpgg, t1.pizhwh, t1.jlgg, t1.kcshl, t1.chbdj, t1.zhjj, AVG(t1.hshj) as hshj from (SELECT  K.spid, K.spbh, K.beactive, K.spmch, K.dw, K.shpchd, K.shpgg, K.pizhwh, K.jlgg, C.kcshl, C.chbdj, C.zhjj,X.dj,X.hshj FROM spkfk K LEFT JOIN spkfjc C ON K.spid=C.spid LEFT JOIN pf_ckmx X ON K.spid = X.spid WHERE K.spid <>ALL (SELECT SPID FROM erpshopinfor)`
            let endSql = `) as t1 group by t1.spid ,t1.spbh,t1.beactive,t1.spmch,t1.dw, t1.shpchd, t1.shpgg, t1.pizhwh, t1.jlgg, t1.kcshl, t1.chbdj, t1.zhjj) AS t2 ) as t3 WHERE t3.PX BETWEEN ${body.size * (body.index - 1) + 1} and ${body.size * body.index}`
            if (body.spmch && body.spmch != 'undefined') {
                startSql += ` AND K.spmch LIKE '%${body.spmch}%'`
                startAllSql += ` AND K.spmch LIKE '%${body.spmch}%'`
            }

            if (body.spbh && body.spbh != 'undefined') {
                startSql += ` AND K.spbh LIKE '%${body.spbh}%'`
                startAllSql += ` AND K.spbh LIKE '%${body.spbh}%'`
            }

            if (body.shpchd && body.shpchd != 'undefined') {
                startSql += ` AND K.shpchd LIKE '%${body.shpchd}%'`
                startAllSql += ` AND K.shpchd LIKE '%${body.shpchd}%'`
            }

            if (body.spid && body.spid != 'undefined') {
                startSql += ` AND K.spid LIKE '%${body.spid}%'`
                startAllSql += ` AND K.spid LIKE '%${body.spid}%'`
            }

            if (body.pizhwh && body.pizhwh != 'undefined') {
                startSql += ` AND K.pizhwh LIKE '%${body.pizhwh}%'`
                startAllSql += ` AND K.pizhwh LIKE '%${body.pizhwh}%'`
            }
            startSql += endSql
            startAllSql += endAllSql
            const getAllSql = () => sql.query(startAllSql)
            const getSql = () => sql.query(startSql)
            let totalList = await getAllSql()
            let results = await getSql()
            ctx.body = { total: totalList.recordset.length, results: results.recordset }
        } catch (err) {
            ctx.body = err
        }
    })


    // 获得改变后的数据，并存入数据库
    router.post('/getAddErpData', async ctx => {
        try {
            const body = ctx.request.body 
            let uploadShopInfor = await transformShopInforIndex(body, 1)
            let uploadShop_num = await transformShopInforIndex(body, 1)
            let shopInforCode = await httprequest('http://erpsrv.mypharma.com/goods/batchSnyc', uploadShopInfor.shopInfor)
            let shop_numCode = await httprequest('http://erpsrv.mypharma.com/inventory/batchSnyc', uploadShop_num.shop_num)
            if(shopInforCode === '000000' && shop_numCode === '000000'){
                const addSql = () => {
                    let startSql = `INSERT INTO erpshopinfor (SPID,SPBH,KCSHL,DW,SHPCHD,SHPGG,PIZHWH,JLGG,CHBDJ,ZHJJ,SPMCH,HSHJ,BEACTIVE)`
                    let endSql = ``
                    body.forEach((e, i) => {
                        if (body.length === (i + 1)) {
                            endSql += ` SELECT '${e.spid}','${e.spbh}', ${e.kcshl}, '${e.dw}', '${e.shpchd}', '${e.shpgg}' ,'${e.pizhwh}',${e.jlgg},${e.chbdj},${e.zhjj},'${e.spmch}',${e.hshj},'${e.beactive}'`
                        } else {
                            endSql += ` SELECT '${e.spid}','${e.spbh}', ${e.kcshl}, '${e.dw}', '${e.shpchd}', '${e.shpgg}' ,'${e.pizhwh}',${e.jlgg},${e.chbdj},${e.zhjj},'${e.spmch}',${e.hshj},'${e.beactive}' UNION`
                        }
                    })
                    startSql += endSql
                    return sql.query(startSql)
                }
                let results = await addSql()

                if (results.rowsAffected[0] > 0) {
                    ctx.body = { sucess: true }
                }
            } else {
                ctx.body = { sucess: false }
            }

        } catch (err) {
            ctx.body = err
        }
    })
    // 获得改变后的药品数据
    router.get('/getChangeShopInfor', async ctx => {
        try {
            const body = ctx.query
            const getAllSql = () => sql.query`SELECT * from erpshopinfor`
            const getSql = () => sql.query`SELECT * FROM (
            SELECT *, ROW_NUMBER() OVER(ORDER BY t1.spid desc) AS ROW FROM
            (SELECT * from erpshopinfor) as t1
        ) as t2
        WHERE t2.Row BETWEEN ${body.size * (body.index - 1) + 1} and ${body.size * body.index}`
            let totalList = await getAllSql()
            let results = await getSql()
            ctx.body = { total: totalList.recordset.length, results: results.recordset }
        } catch (err) {
            ctx.body = err
        }
    })

    // 编辑改变后的药品数据
    router.put('/updateChangeShopInfor', async ctx => {
        try {
            const body = ctx.request.body[0]
            let uploadShopInfor = await transformShopInforLargerIndex([body], 1)
            let uploadShop_num = await transformShopInforLargerIndex([body], 1)
            let shopInforCode = await httprequest('http://erpsrv.mypharma.com/goods/batchSnyc', uploadShopInfor.shopInfor)
            let shop_numCode = await httprequest('http://erpsrv.mypharma.com/inventory/batchSnyc', uploadShop_num.shop_num)
            if(shopInforCode === '000000' && shop_numCode === '000000'){
                const updateSql = () => {
                    return sql.query`UPDATE erpshopinfor SET KCSHL= ${body.KCSHL},HSHJ= ${body.HSHJ} WHERE SPID = ${body.SPID}`
                }
                let results = await updateSql()
                ctx.body = { sucess: true }
            } else {
                ctx.body = { sucess: false }
            }

        } catch (err) {
            ctx.body = err
        }
    })




})


app
    .use(cors())
    .use(bodyParser())
    .use(router.routes())
    .use(router.allowedMethods())

app.listen(3000)